home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Software Vault: The Gold Collection
/
Software Vault - The Gold Collection (American Databankers) (1993).ISO
/
cdr11
/
pdox693.zip
/
TI127.ASC
< prev
next >
Wrap
Text File
|
1993-02-23
|
8KB
|
265 lines
PRODUCT : Paradox NUMBER : 127
VERSION : All
OS : DOS
DATE : February 23, 1993 PAGE : 1/4
TITLE : Normalizing a Non-normalized Paradox Table
This Technical Information sheet demonstrates the best
interactive method for converting a non-normalized table into a
table in the first normal form. In brief, a non-normalized table
is one in which repeating groups of information are spread out
over many fields, rather than placing them into a few fields with
many records. For example, suppose you have a table which tracks
orders placed by a number of customers over a single year. We
want this table to display the customer number and the number of
products purchased for each month. For simplicity's sake, assume
the year only has three months (January, February, and March).
Below is the non-normalized version of this table (OrdersA):
OrdersA═╦═Customer #═╦═January═╦═February═╦═March═╗
║ 0001 ║ 3 ║ 7 ║ 0 ║
║ 0002 ║ 0 ║ 1 ║ 0 ║
║ 0003 ║ 0 ║ 0 ║ 14 ║
Below is the normalized version (the table is called OrdersB):
OrdersB═╦═Customer #═╦═══Month═══╦═Quantity═╗
║ 0001 ║ January ║ 3 ║
║ 0001 ║ February ║ 7 ║
║ 0001 ║ March ║ 0 ║
║ 0002 ║ January ║ 0 ║
║ 0002 ║ February ║ 1 ║
║ 0002 ║ March ║ 0 ║
║ 0003 ║ January ║ 0 ║
║ 0003 ║ February ║ 0 ║
║ 0003 ║ March ║ 14 ║
The first table is keyed on Customer # only. All information
related to a single customer is stored in a single record with
many fields. The second table is keyed on both Customer # and
Month. Each customer has three records (twelve if we were
dealing with a full year), one for each month, and that month's
corresponding quantity. It is necessary to perform a query or
possibly a series of queries to convert the data from the first
table to the normalized format of the second table. To begin the
process, create a new table with the proper structure (one
structured like the OrdersB table). Once this is done, set up
the query as shown on the next page:
PRODUCT : Paradox NUMBER : 127
VERSION : All
OS : DOS
DATE : February 23, 1993 PAGE : 2/4
TITLE : Normalizing a Non-normalized Paradox Table
Bring up the tables, OrdersA and OrdersB, with <F10> Ask
OrdersA═╦═Customer #═╦═January═╦═February═╦═March═══╗
║ _Cust ║ _Quant1 ║ ║ ║
║ _Cust ║ ║ _Quant2 ║ ║
║ _Cust ║ ║ ║ _Quant3 ║
OrdersB═╦═Customer #═╦═══Month═══╦═Quantity═╗
Insert ║ _Cust ║ January ║ _Quant1 ║
Insert ║ _Cust ║ February ║ _Quant2 ║
Insert ║ _Cust ║ March ║ _Quant3 ║
In the query forms above, example elements are represented by an
underscore "_" preceding the label of the example element (i.e.
_Cust and _Quant1). Example elements are created by pressing the
<F5> key and then typing a label for the example element. On row
1, the insert query links the OrdersA Customer # field to the
OrdersB Customer # field (by the example element Cust) and the
OrdersA January Quantity field to the OrdersB Quantity field (by
the example element Quant1). On row 2, the OrdersA Customer #
field is linked to the OrdersB Customer # field (by the example
element Cust) and the OrdersA February Quantity field is linked
to the OrdersB Quantity field (by the example element Quant2).
Use a new row for each field from the source that is being
inserted into the single target field. For example, if the table
has a full year (twelve months), then the query would have twelve
rows of links. The example element representing the quantity
must be different for each line in the query. In this example we
chose to number them (i.e. Quant1, Quant2, Quant3). The key word
"Insert" tells Paradox to insert new records in the OrdersB table
by copying the data from the OrdersA fields which contain example
elements (i.e. Cust and Quant1) to their corresponding fields in
OrdersB.
Paradox versions 3.x and earlier allow a maximum of 22 lines in a
query form. Version 4.0 allows a maximum of 64 lines. If you
require more lines in a query form than is allowed by Paradox,
then break up the query form into two or more parts and save the
queries with different names using Scripts | QuerySave. Each
script must be played to complete the entire query operation
using Scripts | Play.
PRODUCT : Paradox NUMBER : 127
VERSION : All
OS : DOS
DATE : February 23, 1993 PAGE : 3/4
TITLE : Normalizing a Non-normalized Paradox Table
In the previous example, if Paradox only allowed 2 lines in a
query form, then it would be necessary to break up the query into
two parts as shown below:
Query #1
OrdersA═╦═Customer #═╦═January═╦═February═╦═March═╗
║ _Cust ║ _Quant1 ║ ║ ║
║ _Cust ║ ║ _Quant2 ║ ║
OrdersB═╦═Customer #═╦═══Month═══╦═Quantity═╗
Insert ║ _Cust ║ January ║ _Quant1 ║
Insert ║ _Cust ║ February ║ _Quant2 ║
Query #2
OrdersA═╦═Customer #═╦═January═╦═February═╦═March══╦
║ _Cust ║ ║ ║ _Quant1║
OrdersB═╦═Customer #═╦═══Month═══╦═Quantity═╗
Insert ║ _Cust ║ March ║ _Quant1 ║
Notes:
1. This is a generic example, however, the concept is
applicable to any database in which the repeating fields
contain the same type of data.
2. After setting up the query, save it with Scripts |
QuerySave. To process the query, first clear all images
off the screen with <Alt-F8>, then play the querysave
script and press <F2>. The method outlined in this
Technical Information sheet enables you to use a non-
normalized table for data-entry purposes and to create a
query to quickly convert the data to a normalized version
for reporting purposes.
PRODUCT : Paradox NUMBER : 127
VERSION : All
OS : DOS
DATE : February 23, 1993 PAGE : 4/4
TITLE : Normalizing a Non-normalized Paradox Table
3. For further discussion of the theory behind normalization,
refer to Technical Information sheet 124. For additional
information about queries, refer to Chapter 5 and 6 of the
Paradox 4.0 User's Guide (for versions earlier than 4.0,
refer to Chapter 4 of the Paradox User's Guide).
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.